﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.IO.Compression;
using System.Collections.ObjectModel;
using System.Data.SqlServerCe;
using System.Xml.Linq;

using System.Windows.Forms;
using System.Data;
using System.Configuration;
using System.Transactions;
using System.ComponentModel;
using System.Security.Cryptography;
using System.Diagnostics;




namespace MercuryFE
{
    static partial class LauncherLib
    {

        public static string getCurrentDirectory()
        {
            string currentDir = AppDomain.CurrentDomain.BaseDirectory.ToString();
            return (currentDir);
        }

        public static string getDBConnection() {
            string currentDir = getCurrentDirectory();
            string connectionString = "Data Source=" + currentDir + (!currentDir.EndsWith("\\")?"\\":"") + "LauncherDB.sdf; Max Database Size=512";

            return connectionString;
        }

        private static bool truncateTable(SqlCeConnection conn, string tableName, string identityColumn, ref string error)
        {
            // no support for truncate in sql ce - delete and reset identity (if specified)
            string sql = "delete from " + tableName;
            using (SqlCeCommand ceComm = new SqlCeCommand(sql, conn)) { ceComm.ExecuteNonQuery(); }

            if (identityColumn != "")
            {
                sql = "alter table " + tableName + " alter column " + identityColumn + " identity (1,1);";
                using (SqlCeCommand ceComm = new SqlCeCommand(sql, conn)) {
                    try
                    {
                        ceComm.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        error = "Truncate table [" + tableName + "] : " + e.Message;
                        return false;
                    }
                }
            }
            return true;
        }

        public static string getFolderSelection()
        {
            string folder = "";

            FolderBrowserDialog fbd = new FolderBrowserDialog();

            fbd.ShowDialog();
            folder = fbd.SelectedPath;
            return folder;
        }

        public static string getFileSelection(string fileMask)
        {
            string fileName = "";

            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = fileMask;
            ofd.Multiselect = false;
            ofd.ShowDialog();
            fileName = ofd.FileName;

            return fileName;
        }

        public static string getNewFileSelection(string fileMask, string defaultName)
        {
            string fileName = "";

            SaveFileDialog ofd = new SaveFileDialog();

            ofd.Filter = fileMask;

            if (defaultName != null)
                ofd.FileName = defaultName;
    
            ofd.ShowDialog();
            fileName = ofd.FileName;

            return fileName;
        }

        public static bool deleteMenuEntry(int menuId, ref string error)
        {
            // go through and delete all records for child menus before deleting this one.

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    int level = 0;
                    Dictionary<int, int> menusToDelete = new Dictionary<int, int>();
                    menusToDelete.Add(menuId,level);

                    int currentMenuId = menuId;

                    bool recsFound = true;

                    while (recsFound)
                    {
                        recsFound = false;

                        Dictionary<int, int> currentLevel = null;
                        foreach (KeyValuePair<int, int> menuEntry in menusToDelete)
                        {
                            if (menuEntry.Value == level)
                            {
                                currentLevel = (from x in db.Menu
                                                where x.ParentMenuID == menuEntry.Key
                                                select x).ToDictionary(f => f.MenuID, g => level+1);

                            }
                        }
                        foreach (KeyValuePair<int, int> menuEntry in currentLevel)
                        {
                            menusToDelete.Add(menuEntry.Key, menuEntry.Value);
                            if(!recsFound) 
                                recsFound = true;
                        }
                        level++;

    

                    }

                    int maxLevel = -1;
                    foreach (KeyValuePair<int, int> menuEntry in menusToDelete)
                    {
                        if (menuEntry.Value > maxLevel)
                            maxLevel = menuEntry.Value;
                    }

                    for (level = maxLevel; level >= 0; level--)
                    {
                        foreach (KeyValuePair<int, int> menuEntry in menusToDelete)
                        {
                            if (menuEntry.Value == level)
                            {

                                db.MenuCollection.DeleteAllOnSubmit(db.MenuCollection.Where(x => x.MenuID==menuEntry.Key));
                                db.Menu.DeleteAllOnSubmit(db.Menu.Where(x => x.MenuID == menuEntry.Key));

                                try
                                {
                                    db.SubmitChanges();
                                }
                                catch (Exception e)
                                {
                                    error = "Deleting menu : " + e.Message;
                                    return false;
                                }


                            }
                        }
                    }


                }
            }
            return true;
        }


        /// <summary>
        /// Finds all the games for a particular menu selection.
        /// </summary>
        /// <param name="menuId">id of the menu from the database</param>
        /// <returns></returns>
        public static DataTable getGamesByMenu(int menuId)
        {
            List<GameMenu> gameMenu = null;
            
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    gameMenu = (from g in db.Game
                                  join e in db.Emulator on g.EmulatorID equals e.EmulatorID
                                  join mc in db.MenuCollection on g.GameID equals mc.GameID
                                  where mc.MenuID.Equals(menuId)
                                  select new GameMenu
                                  {
                                        GameName = g.Name
                                      , EmulatorName = e.Name
                                      , Description = g.Description
                                      , RomFile = g.RomFile
                                      , GameID = g.GameID
                                      , MenuID = mc.MenuID

                                  }).ToList(); 

                }

            }

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("GameName", typeof(string)));
            dt.Columns.Add(new DataColumn("EmulatorName", typeof(string)));
            dt.Columns.Add(new DataColumn("Description", typeof(string)));
            dt.Columns.Add(new DataColumn("RomFile", typeof(string)));
            dt.Columns.Add(new DataColumn("GameID", typeof(int)));
            dt.Columns.Add(new DataColumn("MenuID", typeof(int)));

            foreach (GameMenu item in gameMenu)
            {
                DataRow dr = dt.NewRow();

                dr["GameName"] = item.GameName;
                dr["EmulatorName"] = item.EmulatorName;
                dr["Description"] = item.Description;
                dr["RomFile"] = item.RomFile;
                dr["GameID"] = item.GameID;
                dr["MenuID"] = item.MenuID;

                dt.Rows.Add(dr);

            }


            return dt;


        }

        //public static bool updateGamesMenu(DataTable dt, ref string error)
        //{
        //    using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
        //    {
        //        ceConn.Open();

        //        using (LauncherDB db = new LauncherDB(ceConn)) // linq
        //        {

        //            foreach (DataRow row in dt.Rows)
        //            {
        //                if (row.RowState == DataRowState.Deleted)
        //                {
        //                    int gameId = (int)row["GameID"];
        //                    int menuId = (int)row["MenuID"];

        //                    var record = db.MenuCollection.Single(c => c.MenuID == menuId && c.GameID == gameId);

        //                    db.MenuCollection.DeleteOnSubmit(record);
        //                }
        //            }
        //            try
        //            {
        //                db.SubmitChanges();
        //            }
        //            catch (Exception e)
        //            {
        //                error = "Updating menu : " + e.Message;
        //                return false;
        //            }
					
        //        }
        //    }
        //    return true;
        //}

        /// <summary>
        /// Populates all required fields for the search games lookup page
        /// </summary>
        /// <param name="emulators"></param>
        /// <param name="nPlayers"></param>
        /// <param name="categories"></param>
        public static void getSearchParameterLookups(ref Dictionary<int,string> emulators, ref List<String> nPlayers, ref List<String> categories
            , ref List<String> controlTypes, ref List<string> manufacturers, ref List<string> driverStatus, ref List<string> biosFiles, ref Dictionary<int, string> mameEmulator)
        {

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    emulators = (from e in db.Emulator
                                 //where e.EmulatorID > 0 // ignore the custom entry
                                 orderby e.IsMame descending
                                 select new { e.EmulatorID, e.Name }).ToDictionary(x=> x.EmulatorID, y => y.Name);
                    emulators.Add(-1, "[ALL]");

                    // only populate mame emulators..
                    mameEmulator = (from e in db.Emulator
                                    where e.IsMame==1 && e.EmulatorID > 0
                                    select new { e.EmulatorID, e.Name }).ToDictionary(x => x.EmulatorID, y => y.Name);

                    // MHFIX
                    nPlayers = (from n in db.MameNPlayers
                                where n.NPlayers != null
                                select n.NPlayers).Distinct().ToList();
                    nPlayers.Insert(0, "[ALL]");

                    categories = (from c in db.MameCategory
                                  select c.CategoryName).ToList();
                    categories.Insert(0, "[ALL]");

                    //controlTypes = (from ct in db.MameControl
                    //                select ct.Control).ToList();

                    controlTypes = (from ct in db.MameConstant
                                    select (ct.Constant==""?"[BLANK]":ct.Constant)).ToList();

                    manufacturers = (from m in db.MameRom
                                     select m.Manufacturer).Distinct().ToList();

                    driverStatus = (from m in db.MameRom
                                    select m.DriverStatus).Distinct().ToList();

                    biosFiles = (from s in db.MameRom
                                 where s.IsBios == 1
                                 select s.RomName).Distinct().ToList();

                    
                }
            }
        }

        public enum SearchType
        {
            Contains, StartsWith, EndsWith
        };

        public static bool HasMameEmulatorDefined()
        {
            bool mameDefined = false;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    mameDefined = (from e in db.Emulator
                                   where e.IsMame == 1
                                   select e).FirstOrDefault() == null ? false : true;

                }
            }

            return mameDefined;
        }

        public static bool IsEmulatorMame(int EmulatorID)
        {
            bool mameSearch = false;
            // 0 is for ALL Games
            if (EmulatorID > 0)
            {
                using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
                {
                    ceConn.Open();

                    using (LauncherDB db = new LauncherDB(ceConn)) // linq
                    {
                        mameSearch = db.Emulator.Single(x => x.EmulatorID == EmulatorID).IsMame == 1 ? true : false;
                    }
                }
            }
            return mameSearch;
        }

        /// <summary>
        /// Finds all matching mame rom files on the file system, based on search criteria. (To be used for copying roms)
        /// </summary>
        public static DataTable searchMameRomFiles(string sourceFolder, string Name, SearchType NameSearchType,
            string RomFile, SearchType RomFileSearchType, string NPlayers, string Category, bool AllowClones, string[] DriverStatus,
            string[] controlType, string[] manufacturers, int maxNumButtons, string[] sourceFiles)
        {

            // generate list of actual files found
            Dictionary<string, string> sourceRomFiles = new Dictionary<string, string>();

            foreach (string file in Directory.GetFiles(sourceFolder, "*.zip", SearchOption.AllDirectories))
            {
                string romfile = Path.GetFileNameWithoutExtension(file);
               // string fullpath = sourceFolder + "\\" + file;
                sourceRomFiles.Add(romfile, file);
            }

            List<string[]> searchRoms = null;

            Dictionary<string, string[]> romDependencies = null;

            // now get list of rom files matching the criteria
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    var mameQuery = db.MameRom.Select(mq => new {mq.RomName, mq.MameRomID, mq.DriverStatus, mq.CloneOf, mq.Manufacturer, mq.SourceFile, mq.RomOf, mq.GameName }); 

                    if (DriverStatus != null)
                        mameQuery = mameQuery.Where(x => DriverStatus.Contains(x.DriverStatus));

                    if (!AllowClones)
                        mameQuery = mameQuery.Where(x => x.CloneOf == null);

                    if (NPlayers != "")
                        mameQuery = mameQuery.Join(db.MameNPlayers, mq => mq.MameRomID, np => np.MameRomID, (mq, np) => new { mq, np.NPlayers }).Where(x => x.NPlayers == NPlayers).Select(x => x.mq);

                    if (Category != "")
                        mameQuery = mameQuery.Join(db.MameRomCategory, mq => mq.MameRomID, mrc => mrc.MameRomID, (mq, mrc) => new { mq, mrc.MameCategoryID })
                            .Join(db.MameCategory, mq => mq.MameCategoryID, mc => mc.MameCategoryID, (mq, mc) => new { mq.mq, mc.CategoryName })
                            .Where(x => x.CategoryName == Category)
                            .Select(x => x.mq);

                    if (controlType != null)
                    {

                        if (controlType.Contains("[BLANK]"))
                        {
                            for (int i = 0; i < controlType.Length; i++)
                            {
                                if (controlType[i] == "[BLANK]")
                                    controlType[i] = "";
                            }
                        }


                        mameQuery = mameQuery.Join(db.MamePlayer, mq => mq.MameRomID, mp => mp.MameRomID, (mq, mp) => new { mq, mp.MamePlayerID })
                            .Join(db.MamePlayerControl, mq => mq.MamePlayerID, mpc => mpc.MamePlayerID, (mq, mpc) => new { mq.mq, mpc.MamePlayerControlID })
                            .Join(db.MameControlConstant, mq => mq.MamePlayerControlID, mcc => mcc.MamePlayerControlID, (mq, mcc) => new { mq.mq, mcc.MameConstantID })
                            .Join(db.MameConstant, mq => mq.MameConstantID, mc => mc.MameConstantID, (mq, mc) => new { mq.mq, mc.Constant })
                            .Where(ct => controlType.Contains(ct.Constant))
                            .Select(mq => mq.mq);

                    }

                    if (maxNumButtons < 6)
                        mameQuery = mameQuery.Join(db.MamePlayer, mq => mq.MameRomID, mp => mp.MameRomID, (mq, mp) => new { mq, mp.NumberOfButtons })
                            .Where(mp => mp.NumberOfButtons <= maxNumButtons)
                            .Select(mq => mq.mq);

                    if (manufacturers != null)
                        mameQuery = mameQuery.Where(mq => manufacturers.Contains(mq.Manufacturer)).Select(mq => mq);

                    if (sourceFiles != null)
                        mameQuery = mameQuery.Where(mq => sourceFiles.Contains(mq.SourceFile)).Select(mq => mq);



                    // common to all
                    if (Name != "")
                    {
                        switch (NameSearchType)
                        {
                            case SearchType.Contains:
                                mameQuery = mameQuery.Where(x => x.GameName.ToUpper().Contains(Name.ToUpper()));
                                break;
                            case SearchType.EndsWith:
                                mameQuery = mameQuery.Where(x => x.GameName.ToUpper().EndsWith(Name.ToUpper()));
                                break;
                            case SearchType.StartsWith:
                                mameQuery = mameQuery.Where(x => x.GameName.ToUpper().StartsWith(Name.ToUpper()));
                                break;
                        }
                    }

                    if (RomFile != "")
                    {
                        switch (RomFileSearchType)
                        {
                            case SearchType.Contains:
                                mameQuery = mameQuery.Where(x => x.RomName.ToUpper().Contains(RomFile.ToUpper()));
                                break;
                            case SearchType.EndsWith:
                                mameQuery = mameQuery.Where(x => x.RomName.ToUpper().EndsWith(RomFile.ToUpper()));
                                break;
                            case SearchType.StartsWith:
                                mameQuery = mameQuery.Where(x => x.RomName.ToUpper().StartsWith(RomFile.ToUpper()));
                                break;
                        }
                    }



                    searchRoms = mameQuery.Select(x => new string[]{ x.RomName, x.RomOf }).Distinct().ToList();



                    romDependencies = (from x in db.MameRomDependencies
                                       select new { x.RomName, x.BiosRomName, x.ParentRomName }).ToDictionary(
                                        a => a.RomName, b=> new string[] {b.BiosRomName, b.ParentRomName}
                                      );
                }
            }

            DataTable dt = new DataTable();
            dt.Columns.Add("RomFile", typeof(string));
            dt.Columns.Add("SourcePath", typeof(string));
            dt.Columns.Add("BiosRom", typeof(string));
            dt.Columns.Add("ParentRom", typeof(string));
            dt.Columns.Add("Added Dependency", typeof(bool));
            dt.Columns.Add("Valid", typeof(bool));
           // dt.Columns.Add("MameRomID", typeof(int));


            List<string> actualRoms = new List<string>();
            List<string> addedRoms = new List<string>();

            foreach (string[] searchRom in searchRoms)
            {
                if (sourceRomFiles.ContainsKey(searchRom[0]))
                {
                    actualRoms.Add(searchRom[0]);

                    string biosRom = null, parentRom = null;
                    // check for dependencies
                    if (romDependencies.ContainsKey(searchRom[0]))
                    {
                        biosRom = romDependencies[searchRom[0]][0];
                        parentRom = romDependencies[searchRom[0]][1];

                        if (biosRom != null)
                        {
                            addedRoms.Add(biosRom);
                        }
                        if (parentRom != null)
                        {
                            addedRoms.Add(parentRom);
                        }
                    }

                    dt.Rows.Add(searchRom[0],  sourceRomFiles[searchRom[0]], biosRom, parentRom, false, true);
                }
            }

            //distinct dependent roms which ARE NOT already in the actual Roms
            addedRoms = addedRoms.Distinct().Where(x => !actualRoms.Contains(x)).ToList();

            foreach (string entry in addedRoms)
            {
                // check if part of source rom files...
                if (sourceRomFiles.ContainsKey(entry))
                {
                    dt.Rows.Add(entry, sourceRomFiles[entry], null, null, true, true);
                }
                else
                {
                    dt.Rows.Add(entry, "NOT FOUND", null, null, true, false);
                }

            }




            // need to ensure all 'required' roms are also included (parent, and bios)

            return dt;
        }

        public static bool copyMameRomFiles(DataTable sourceFiles, int emulatorId, ref string error)
        {
            // few notes about copying mame files...

            // if 


            string destFolder = null;
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    destFolder = (from e in db.Emulator
                                join ep in db.EmulatorPath on e.EmulatorID equals ep.EmulatorID
                                where e.EmulatorID == emulatorId
                                && ep.PathType == "ROM"
                                select ep.Path).SingleOrDefault();
                }
            }

            if (destFolder != null && destFolder != "")
            {



                    foreach (DataRow dr in sourceFiles.Rows)
                    {
                        if ((bool)dr["Valid"])
                        {
                            string sourcePath = dr["SourcePath"].ToString();
                            string destPath = destFolder + "\\" + Path.GetFileName(sourcePath);

                            try
                            {
                                File.Copy(sourcePath, destPath, true);

                            }
                            catch (Exception e)
                            {
                                error = "Copying " + sourcePath + " to " + destPath + " : " + e.Message;
                                return false;
                            }
                        }
                    }
            }

            return true;
        }

        private static string getSearchQuerySQL(KeyValuePair<int, string> emulator, string Name, SearchType NameSearchType,
            string RomFile, SearchType RomFileSearchType, string NPlayers, string Category, bool AllowClones, string[] DriverStatus,
            string[] controlType, string[] manufacturers, int maxNumButtons, string[] biosFiles, string searchType)
        {
            // searchType -> "" = standard, "category" = put in category value

            bool mameSearch = false;
            bool otherSearch = false;
            string union = "";
            string otherSQL = "";
            string mameSQL = "";

            bool categorySearch = false;
            if (searchType == "category")
            {
                categorySearch = true;
            }

            if (emulator.Key == -1) // ALL
            {
                // if search for everything - then need both mame and other
                mameSearch = true;
                otherSearch = true;
                union = " union ";
            }
            else
            {
                // otherwise, mame is opposite of other (ie, if searching for mame, don't need to search for other, and vice versa)
                mameSearch = IsEmulatorMame(emulator.Key);
                otherSearch = !mameSearch;
            }


            if (otherSearch)
            {
                otherSQL =
                "select distinct " +
                " g.Name " +
                ",e.Name as Emulator " +
                ",g.Description " +
                ",g.RomFile " +
                ",null as DriverStatus " +
                ",null as CloneOf " +
                ",null as NPlayers " +
                ",g.GameID " +
                (categorySearch ? ",null as category " : "") +
                "from Game g " +
                "join Emulator e " +
                "   on g.EmulatorID = e.EmulatorID " +
                "where e.IsMame = 0 ";

                if (emulator.Key > 0)
                {
                    otherSQL += "and e.EmulatorID = " + emulator.Key + " ";

                }

             
             
            }

            string fromSQL =
                "select distinct " +
                " g.Name " +
                ",e.Name as Emulator " +
                ",g.Description " +
                ",g.RomFile " +
                ",mr.DriverStatus " +
                ",mr.CloneOf " +
                ",np.NPlayers " +
                ",g.GameID " +
                (categorySearch ? ",mc.CategoryName as category " : "") +
                "from Game g " +
                "join Emulator e " +
                "   on g.EmulatorID = e.EmulatorID " +
                "inner join MameRom mr " +
                "   on g.MameRomID = mr.MameRomID " +
                "inner join MameNPlayers np " +
                "   on g.MameRomID = np.MameRomID ";

            string filterSQL = "where IsMame = 1 ";

            if (emulator.Value != null && emulator.Key > 0) // && emulator.Value != "")
                filterSQL += "and e.EmulatorID = '" + emulator.Key + "' ";


            if (Name != "")
            {
                string sqlNameMatch = "";
                switch (NameSearchType)
                {
                    case SearchType.Contains:
                        sqlNameMatch = "%" + Name + "%";
                        break;
                    case SearchType.EndsWith:
                        sqlNameMatch = "%" + Name;
                        break;
                    case SearchType.StartsWith:
                        sqlNameMatch = Name + "%";
                        break;
                }
                filterSQL += "and g.Name like '" + sqlNameMatch + "' ";
                otherSQL += "and g.Name like '" + sqlNameMatch + "' ";

                
            }

            if (RomFile != "")
            {
                string sqlRolmFileMatch = "";
                switch (RomFileSearchType)
                {
                    case SearchType.Contains:
                        sqlRolmFileMatch = "%" + RomFile + "%";
                        break;
                    case SearchType.EndsWith:
                        sqlRolmFileMatch = "%" + RomFile;
                        break;
                    case SearchType.StartsWith:
                        sqlRolmFileMatch = RomFile + "%";
                        break;
                }
                filterSQL += "and g.RomFile like '" + sqlRolmFileMatch + "' ";
                otherSQL += "and g.RomFile like '" + sqlRolmFileMatch + "' ";
            }

            if (mameSearch)
            {

                //string mameFilterSQL = "";

                if (DriverStatus != null)
                {
                   filterSQL += "and mr.DriverStatus in ('" + String.Join("','", DriverStatus) + "') ";
                }

                if (!AllowClones)
                {
                    filterSQL += "and mr.CloneOf is null ";
                }

                if (NPlayers != "")
                {
                    filterSQL += "and np.NPlayers = '" + NPlayers + "' ";
                }

                if (Category != "" || categorySearch)
                {
                    fromSQL += "inner join MameRomCategory mrc " +
                        "   on mr.MameRomID = mrc.MameRomID " +
                        "inner join MameCategory mc " +
                        "   on mrc.MameCategoryID = mc.MameCategoryID ";
                    if(Category != "")
                        filterSQL += "and mc.CategoryName = '" + Category + "' ";

                }


                if (controlType != null || maxNumButtons < 6)
                {

                    if (controlType != null && controlType.Contains("[BLANK]"))
                    {
                        for (int i = 0; i < controlType.Length; i++)
                        {
                            if (controlType[i] == "[BLANK]")
                                controlType[i] = "";
                        }
                    }


                    fromSQL += "inner join " +
                    "(" +
                    "select distinct " +
                    "  mp.MameRomID " +
                    "from MamePlayer mp     ";

                    if(controlType != null) {
                        fromSQL +=
                        "inner join MamePlayerControl mpc     " +
                        "    on mp.MamePlayerID = mpc.MamePlayerID  " +
                        "inner join MameControlConstant mcc     " +
                        "    on mpc.MamePlayerControlID = mcc.MamePlayerControlID  " +
                        "inner join MameConstant mc    " +
                        "    on mcc.MameConstantID = mc.MameConstantID  ";
                    }
                    fromSQL += "where 1 = 1 ";

                    if (controlType != null)
                        fromSQL += "and mc.Constant in ('" + String.Join("','", controlType) + "') ";

                    if (maxNumButtons < 6)
                        fromSQL += "and mp.NumberOfButtons <= " + maxNumButtons.ToString() + " ";

                    fromSQL += ") c " +
                        "on mr.MameRomID = c.MameRomID ";
                }


                if (manufacturers != null)
                {
                    filterSQL += "and mr.Manufacturer in ('" + String.Join("','", manufacturers) + "') ";
                }

                if (biosFiles != null)
                {
                    fromSQL +=
                        "inner join MameRomBios mrb " +
                        "   on mr.MameRomID = mrb.MameRomID " +
                        "inner join MameRom mr2 " +
                        "   on mrb.BiosMameRomID = mr2.MameRomID ";
                    filterSQL += "and mr2.RomName in ('" + String.Join("','", biosFiles) + "') ";
                }
                else
                {
                    // only want game roms (not bios)
                    filterSQL += "and mr.IsBios = 0 ";
                }

                mameSQL = fromSQL + filterSQL;
                
            }
            //fromSQL += filterSQL;
            return (mameSQL + union + otherSQL);
        }


    //    private static IQueryable<SearchGame> getSearchQuery(LauncherDB db, KeyValuePair<int,string> emulator, string Name, SearchType NameSearchType,
    //        string RomFile, SearchType RomFileSearchType, string NPlayers, string Category, bool AllowClones, string[] DriverStatus,
    //        string[] controlType, string[] manufacturers, int maxNumButtons, string[] sourceFiles)
    //    {



    //        bool mameSearch = IsEmulatorMame(emulator.Key);
            

    //        var query = from g in db.Game
    //                    join e in db.Emulator on g.EmulatorID equals e.EmulatorID
    //                    join mr in db.MameRom on g.MameRomID equals mr.MameRomID into m1
    //                    from x in m1.DefaultIfEmpty() join np in db.MameNPlayers on x.MameRomID equals np.MameRomID into m2
    //                    from mameinfo in m2.DefaultIfEmpty()
    //                    select new SearchGame
    //                    {
    //                        Name = g.Name
    //                        ,
    //                        Description = g.Description
    //                        ,
    //                        RomFile = g.RomFile
    //                        ,
    //                        DriverStatus = x.DriverStatus
    //                        ,
    //                        CloneOf = x.CloneOf
    //                        ,
    //                        NPlayers = mameinfo.NPlayers
    //                        ,
    //                        MameRomID = (int?)g.MameRomID
    //                        ,
    //                        GameID = g.GameID
    //                        ,
    //                        Emulator = e.Name
    //                        ,
    //                        SourceFile = x.SourceFile
    //                        ,
    //                        IsMame = ( e.IsMame==1?true:false)
    //                    };

    //        if (emulator.Value != null && emulator.Key > 0 &&  emulator.Value != "")
    //            query = query.Where(x => x.Emulator == emulator.Value);


    //        if (mameSearch)
    //        {
    //            //var mameQuery = from g in db.Game
    //            //                join mr in db.MameRom on g.MameRomID equals mr.MameRomID
    //            //                join np in db.MameNPlayers on mr.MameRomID equals np.MameRomID
    //            //                select g.GameID;


    //            var mameQuery = db.Game.Join(db.MameRom, g => g.MameRomID, mr => mr.MameRomID, (g, mr) => new {g.GameID, mr.DriverStatus, mr.CloneOf, mr.MameRomID, mr.SourceFile, mr.Manufacturer });


    //            if (DriverStatus != null)
    //            {
    //                mameQuery = mameQuery.Where(x => DriverStatus.Contains(x.DriverStatus));
   
    //                //query = query.Where(x => DriverStatus.Contains(x.DriverStatus));
    //            }

    //            if (!AllowClones)
    //            {
    //                mameQuery = mameQuery.Where(x => x.CloneOf == null);
    //               // query = query.Where(x => x.CloneOf == null);
    //            }

    //            if (NPlayers != "")
    //            {
    //                mameQuery = mameQuery.Join(db.MameNPlayers, mq => mq.MameRomID, np => np.MameRomID, (mq, np) => new { mq, np.NPlayers }).Where(x => x.NPlayers == NPlayers).Select(x=> x.mq);
    //                //query = query.Where(x => x.NPlayers == NPlayers);
    //            }

    //            if (Category != "")
    //            {
    //                //query = from x in query
    //                //        join mrc in db.MameRomCategory on x.MameRomID equals mrc.MameRomID
    //                //        join mc in db.MameCategory on mrc.MameCategoryID equals mc.MameCategoryID
    //                //        where mc.CategoryName == Category
    //                //        select x;


    //                mameQuery = mameQuery.Join(db.MameRomCategory, mq => mq.MameRomID, mrc => mrc.MameRomID, (mq, mrc) => new { mq, mrc.MameCategoryID })
    //                    .Join(db.MameCategory, mq => mq.MameCategoryID, mc => mc.MameCategoryID, (mq, mc) => new {mq.mq, mc.CategoryName})
    //                    .Where(x => x.CategoryName==Category)
    //                    .Select(x => x.mq);

    //            }


    //            if (controlType != null)
    //            {

    //                if (controlType.Contains("[BLANK]"))
    //                {
    //                    for (int i = 0; i < controlType.Length; i++)
    //                    {
    //                        if (controlType[i] == "[BLANK]")
    //                            controlType[i] = "";
    //                    }
    //                }
                        

    //                //query = from x in query
    //                //        join mp in db.MamePlayer on x.MameRomID equals mp.MameRomID
    //                //        join mpc in db.MamePlayerControl on mp.MamePlayerID equals mpc.MamePlayerID
    //                //        join mcc in db.MameControlConstant on mpc.MamePlayerControlID equals mcc.MamePlayerControlID
    //                //        join mc in db.MameConstant on mcc.MameConstantID equals mc.MameConstantID
    //                //        where controlType.Contains(mc.Constant)
    //                //        select x;


    //                mameQuery = mameQuery.Join(db.MamePlayer, mq => mq.MameRomID, mp => mp.MameRomID, (mq, mp) => new { mq, mp.MamePlayerID })
    //                    .Join(db.MamePlayerControl, mq => mq.MamePlayerID, mpc => mpc.MamePlayerID, (mq, mpc) => new { mq.mq, mpc.MamePlayerControlID })
    //                    .Join(db.MameControlConstant, mq => mq.MamePlayerControlID, mcc => mcc.MamePlayerControlID, (mq, mcc) => new { mq.mq, mcc.MameConstantID })
    //                    .Join(db.MameConstant, mq => mq.MameConstantID, mc => mc.MameConstantID, (mq, mc) => new { mq.mq, mc.Constant })
    //                    .Where(ct => controlType.Contains(ct.Constant))
    //                    .Select(mq => mq.mq);

    //            }

    //            if (maxNumButtons < 6)
    //            {
    //                //query = from x in query
    //                //        join mp in db.MamePlayer on x.MameRomID equals mp.MameRomID
    //                //        where mp.NumberOfButtons <= maxNumButtons
    //                //        select x;

    //                mameQuery = mameQuery.Join(db.MamePlayer, mq => mq.MameRomID, mp => mp.MameRomID, (mq, mp) => new { mq, mp.NumberOfButtons })
    //                    .Where(mp => mp.NumberOfButtons <= maxNumButtons)
    //                    .Select(mq => mq.mq);
    //            }



    //            if (manufacturers != null)
    //            {
    //                //query = from x in query
    //                //        join mr in db.MameRom on x.MameRomID equals mr.MameRomID
    //                //        where manufacturers.Contains(mr.Manufacturer)
    //                //        select x;

    //                mameQuery = mameQuery.Where(mq => manufacturers.Contains(mq.Manufacturer)).Select(mq => mq);
    //            }

    //            if (sourceFiles != null)
    //            {
    //                //query = from x in query
    //                //        where sourceFiles.Contains(x.SourceFile)
    //                //        select x;

    //                mameQuery = mameQuery.Where(mq => sourceFiles.Contains(mq.SourceFile)).Select(mq => mq);
    //            }

    ////            List<int> gameIds = mameQuery.Select(x => x.GameID).ToList();
    

    //            query = query.Where(q => (mameQuery.Select(x=> x.GameID).Contains(q.GameID) && q.IsMame) || !q.IsMame);

    //        }





    //        // common to all
    //        if (Name != "")
    //        {
    //            switch (NameSearchType)
    //            {
    //                case SearchType.Contains:
    //                    query = query.Where(x => x.Name.ToUpper().Contains(Name.ToUpper()));
    //                    break;
    //                case SearchType.EndsWith:
    //                    query = query.Where(x => x.Name.ToUpper().EndsWith(Name.ToUpper()));
    //                    break;
    //                case SearchType.StartsWith:
    //                    query = query.Where(x => x.Name.ToUpper().StartsWith(Name.ToUpper()));
    //                    break;
    //            }
    //        }

    //        if (RomFile != "")
    //        {
    //            switch (RomFileSearchType)
    //            {
    //                case SearchType.Contains:
    //                    query = query.Where(x => x.RomFile.ToUpper().Contains(RomFile.ToUpper()));
    //                    break;
    //                case SearchType.EndsWith:
    //                    query = query.Where(x => x.RomFile.ToUpper().EndsWith(RomFile.ToUpper()));
    //                    break;
    //                case SearchType.StartsWith:
    //                    query = query.Where(x => x.RomFile.ToUpper().StartsWith(RomFile.ToUpper()));
    //                    break;
    //            }
    //        }


    //        return query.AsQueryable();
    //                //searchGames = query.Distinct().ToList();

    //    }

        public static DataTable searchGames(KeyValuePair<int,string> emulator, string Name, SearchType NameSearchType, 
            string RomFile, SearchType RomFileSearchType, string NPlayers, string Category, bool AllowClones, string[] DriverStatus,
            string[] controlType, string[] manufacturers, int maxNumButtons, string[] sourceFiles)
        {

            //List<SearchGame> searchGames = new List<SearchGame>();
            DataTable dt = new DataTable();
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                string sql = getSearchQuerySQL(emulator, Name, NameSearchType, RomFile, RomFileSearchType, NPlayers, Category, AllowClones,
                        DriverStatus, controlType, manufacturers, maxNumButtons, sourceFiles,"");

                SqlCeDataAdapter da = new SqlCeDataAdapter(sql, ceConn);
                da.Fill(dt);

            }



            return dt;

        }

        public static DataTable searchMusic(string title, string artists, string genre)
        {
            DataTable dt = new DataTable();
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                string sql = "";


                sql = "select JBCollectionID, Name, Genre, Artists "
                + "from JBCollection "
                + "where 1 = 1 ";

                if (title != null)
                    sql += "and Name like '%" + title + "%' ";

                if (artists != null)
                    sql += "and Artists like '%" + artists + "%' ";

                if(genre != null)
                    sql += "and Genre = '" + genre + "' ";
 


                SqlCeDataAdapter da = new SqlCeDataAdapter(sql, ceConn);
                da.Fill(dt);

            }

            return dt;
        }

        /// <summary>
        /// Using the provided datatable source, adds the user selections to the database.
        /// </summary>
        /// <param name="searchGames">DataTable of search results</param>
        public static bool addGames(int menuID, DataTable searchGames, ref string error)
        {
            
            if(searchGames.Rows.Count > 0) {
                using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
                {
                    ceConn.Open();

                    using (LauncherDB db = new LauncherDB(ceConn)) // linq
                    {
                        // grab list of existing games for menu (so we don't insert again)
                        List<int> existingGames = (from x in db.MenuCollection
                                                   where x.MenuID == menuID
                                                   select x.GameID).ToList();


                        foreach (DataRow row in searchGames.Rows)
                        {
                            // if user hasn't deleted, add it in..
                            if (row.RowState != DataRowState.Deleted)
                            {
                                int gameId = (int)row["GameID"];

                                // if not already in collection, add it in
                                if (!existingGames.Contains(gameId))
                                {
                                    MenuCollection mc = new MenuCollection
                                    {
                                        GameID = gameId
                                        ,
                                        MenuID = menuID
                                    };
                                    db.MenuCollection.InsertOnSubmit(mc);
                                }
                            }
                        }
                        try
                        {
                            db.SubmitChanges();
                        }
                        catch (Exception e)
                        {
                            error = "Adding games to menu : " + e.Message;
                            return false;
                        }
                    }
                }
            }
            return true;

        }



        public static bool addMusic(int menuID, DataTable searchMusic, ref string error)
        {

            if (searchMusic.Rows.Count > 0)
            {
                using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
                {
                    ceConn.Open();

                    using (LauncherDB db = new LauncherDB(ceConn)) // linq
                    {
                        // grab list of existing games for menu (so we don't insert again)
                        List<int> existingMusic = (from x in db.MenuCollection
                                                   where x.MenuID == menuID
                                                   select x.GameID).ToList();


                        foreach (DataRow row in searchMusic.Rows)
                        {
                            // if user hasn't deleted, add it in..
                            if (row.RowState != DataRowState.Deleted)
                            {
                                int gameId = (int)row["JBCollectionID"];

                                // if not already in collection, add it in
                                if (!existingMusic.Contains(gameId))
                                {
                                    MenuCollection mc = new MenuCollection
                                    {
                                        GameID = gameId
                                        ,
                                        MenuID = menuID
                                    };
                                    db.MenuCollection.InsertOnSubmit(mc);
                                }
                            }
                        }
                        try
                        {
                            db.SubmitChanges();
                        }
                        catch (Exception e)
                        {
                            error = "Adding music to menu : " + e.Message;
                            return false;
                        }
                    }
                }
            }
            return true;

        }

        /// <summary>
        /// Removes all items under a menu 
        /// </summary>
        /// <param name="menuId"></param>
        public static bool deleteMenuCollection(int menuId, string newMenuType)
        {

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    // check existing menu type, stored in db
                    string currentMenuType = (from m in db.Menu
                                              where m.MenuID == menuId
                                              select m.MenuType).Single();

                    // only if its changed do we 'clear out'
                    if (currentMenuType != newMenuType)
                    {

                        db.MenuCollection.DeleteAllOnSubmit(
                            (from mc in db.MenuCollection
                             where mc.MenuID == menuId
                             select mc).ToList()
                         );
                        db.SubmitChanges();

                        Menu menu = (from m in db.Menu
                                     where m.MenuID == menuId
                                     select m).Single();
                        menu.MenuType = newMenuType;
                        db.SubmitChanges();

                        return true;
                    }
                    else
                        return false;
                }
            }
        }

        //******************************************************************************

     


        private static int SortByLabel(Label object1, Label object2)
        {
            string one = (object1.name == null ? "" : object1.name) + (object1.value == null ? "" : object1.value) + (object1.color == null ? "" : object1.color);
            string two = (object2.name == null ? "" : object2.name) + (object2.value == null ? "" : object2.value) + (object2.color == null ? "" : object2.color);
            return one.CompareTo(two);
        }


        // sort (ideally, implement as Icomparable, for better reuse)
        private static int SortByRomName(GameDetail object1, GameDetail object2)
        {
            // sort ascending
            return -object2.romName.CompareTo(object1.romName);
        }

        private static int SortByRomName(Category object1, Category object2)
        {
            // sort ascending
            return -object2.romName.CompareTo(object1.romName);
        }



        public static void shrinkDB()
        {
            using (SqlCeEngine engine = new SqlCeEngine(getDBConnection()))
            {
                engine.Shrink();
            }

        }

        public static Dictionary<int, string> getAllEmulatorPaths()
        {
            Dictionary<int, string> emulatorPaths = null;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    emulatorPaths = (from  x in db.EmulatorPath
                                     where x.PathType != "ROM"
                                     select x).ToDictionary(q => q.EmulatorPathID, p => p.Path);
                }
            }

            return emulatorPaths;
        }

        public static Dictionary<int, string> getCustomGameList()
        {
            Dictionary<int, string> customGameList = null;
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    customGameList = (from x in db.Game
                                    where x.EmulatorID == 0
                                      select new { x.GameID, x.Name }).ToDictionary(a => a.GameID, b => b.Name);
                }
            }

            return customGameList;
        }

        public static Dictionary<int, string> getEmulatorList()
        {
            
            Dictionary<int, string> emulatorList = null;
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    emulatorList = (from x in db.Emulator
                                    where x.EmulatorID > 0 // don't want 'custom' entry
                                 select new { x.EmulatorID, x.Name }).ToDictionary(a => a.EmulatorID, b => b.Name);
                }
            }

            return emulatorList;
        }

        public static CustomGame getCustomGame(int CustomGameID)
        {
            CustomGame config = new CustomGame();

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    var g = (from x in db.Game
                             where x.GameID == CustomGameID
                             select x).Single();

                    config.Name = g.Name;
                    config.Description = g.Description;
                    config.Executable = g.RomSubPath + (g.RomSubPath.EndsWith("\\") ? "" : "\\") + g.RomFile;
                    //config.ExecutableLocation = g.RomSubPath;
                    config.Movie = g.MovieSubPath + (g.MovieSubPath.EndsWith("\\")?"":"\\") + g.MovieFile;
                    //config.MovieLocation = g.MovieSubPath;
                    config.Snapshot = g.SnapshotSubPath + (g.SnapshotSubPath.EndsWith("\\") ? "" : "\\") + g.SnapshotFile;
                    //config.SnapshotLocation = g.SnapshotSubPath;
                    config.PreLoad = g.PreLoad;
                    config.PostLoad = g.PostLoad;
                    config.KeyMappings = StringToKeyMapping(g.KeyMappings);

                }
            }

            return config;
        }


        public static List<string> getMusicGenreList()
        {

            List<string> genreList = null;
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    //genreList = from db.
                    genreList = (from x in db.JBCollection
                                 select x.Genre).Distinct().ToList();
                }
            }

            return genreList;
        }


        public static EmulatorConfig getEmulatorConfig(int emulatorId)
        {
            EmulatorConfig config = new EmulatorConfig();

            string[] pathTypes = new string[] {"ROM", "MOVIE","SNAP" };

            //Dictionary<int, string> emulatorPaths = null;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    var e = (from x in db.Emulator
                                where x.EmulatorID == emulatorId
                                select x).Single();

                    config.Name = e.Name;
                    config.Description = e.Description;
                    config.Arguments = e.Arguments;
                    config.ConfigurationFile = e.ConfigurationFile;
                    config.Executable = e.Executable;
                    config.ImageName = e.ImageName;
                    config.Location = e.Location;
                    config.IsMame = e.IsMame==1?true:false;
                    config.ConfigDescFile = e.ConfigDescFile;
                    config.PreLoad = e.PreLoad;
                    config.PostLoad = e.PostLoad;
                    config.KeyMappings = LauncherLib.StringToKeyMapping(e.KeyMappings);
        

                    foreach (string pathType in pathTypes)
                    {
                        // load in paths
                        List<EmulatorPathConfig> emulatorPaths = (from x in db.EmulatorPath
                                         where x.PathType == pathType && x.EmulatorID == emulatorId
                                        
                                         select new EmulatorPathConfig {
                                          EmulatorPathID = x.EmulatorPathID
                                          , Path = x.Path
                                          , IncludeSubPaths = x.IncludeSubPaths == 0 ? false: true
                                          , SearchPattern = x.SearchPattern
                                         }).ToList();

                        switch (pathType)
                        {
                            case "ROM": config.RomPaths = emulatorPaths;
                                break;

                            case "MOVIE": config.MoviePaths = emulatorPaths;
                                break;

                            case "SNAP": config.SnapPaths = emulatorPaths;
                                break;
                        }
                    }

                    if (config.RomPaths.Count > 0)
                    {
                        config.RomIncludeSubPaths = config.RomPaths[0].IncludeSubPaths;
                        config.RomSearchPattern = config.RomPaths[0].SearchPattern;
                    }
                    if (config.SnapPaths.Count > 0)
                    {
                        config.SnapIncludeSubPaths = config.SnapPaths[0].IncludeSubPaths;
                        config.SnapSearchPattern = config.SnapPaths[0].SearchPattern;
                    }
                    if (config.MoviePaths.Count > 0)
                    {
                        config.MovieIncludeSubPaths = config.MoviePaths[0].IncludeSubPaths;
                        config.MovieSearchPattern = config.MoviePaths[0].SearchPattern;
                    }
                }
            }

            return config;
        }

        public static int saveEmulatorConfig(EmulatorConfig config, ref string error)
        {
            int emulatorId = config.EmulatorID;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    bool insert = true;
                    Emulator e = null;
                    if (emulatorId > 0)
                    {
                        e = db.Emulator.Single(x => x.EmulatorID == emulatorId);
                        insert = false;
                    }
                    else // insert
                        e = new Emulator();


                    e.Name = config.Name;
                    e.Description = config.Description;
                    e.Location = config.Location;
                    e.ImageName = config.ImageName;
                    e.Executable = config.Executable;
                    e.Arguments = config.Arguments;
                    e.ConfigurationFile = config.ConfigurationFile;
                    e.IsMame = (byte)(config.IsMame?1:0);
                    e.ConfigDescFile = config.ConfigDescFile;
                    e.PreLoad = config.PreLoad;
                    e.PostLoad = config.PostLoad;
                    e.KeyMappings = config.KeyMappingString();

                    if (insert)
                        db.Emulator.InsertOnSubmit(e);
                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        error = "Saving emulator record : " + ex.Message;
                        return -1;
                    }

                    emulatorId = e.EmulatorID;

                    if(!saveEmulatorPathConfig(emulatorId, config.RomPaths, "ROM", db, insert, ref error))
                        return -1;

                    if(!saveEmulatorPathConfig(emulatorId, config.SnapPaths, "SNAP", db, insert, ref error))
                        return -1;
                    
                    if(!saveEmulatorPathConfig(emulatorId, config.MoviePaths, "MOVIE", db, insert, ref error)) 
                        return -1;


                }
            }

            return emulatorId;

        }

        public static int? saveCustomGameConfig(CustomGame config, ref string error)
        {
            int? gameId = config.GameID;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    bool insert = true;
                    Game g = null;
                    if (gameId != null && gameId > 0)
                    {
                        g = db.Game.Single(x => x.GameID == gameId);
                        insert = false;
                    }
                    else // insert
                        g = new Game();


                    g.Name = config.Name;
                    g.Description = config.Description;
                    g.RomFile = Path.GetFileName(config.Executable);
                    g.RomSubPath = Path.GetDirectoryName(config.Executable);
                    g.MovieFile = Path.GetFileName(config.Movie);
                    g.MovieSubPath = Path.GetDirectoryName(config.Movie);
                    g.SnapshotFile = Path.GetFileName(config.Snapshot);
                    g.SnapshotSubPath = Path.GetDirectoryName(config.Snapshot);
                    g.PreLoad = config.PreLoad;
                    g.PostLoad = config.PostLoad;
                    g.KeyMappings = config.KeyMappingString();

                    // not null default value..
                    g.RomPathID = 0;

                    if (insert)
                        db.Game.InsertOnSubmit(g);

                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Saving custom game : " + e.Message;
                        return -1;
                    }

                    gameId = g.GameID;


                }
            }

            return gameId;

        }

        private static bool saveEmulatorPathConfig(int emulatorId, List<EmulatorPathConfig> paths, string pathType, LauncherDB db, bool insert, ref string error)
        {

            // now save the paths
            // ROM                        
            Dictionary<string, int> currentPaths = (from x in db.EmulatorPath
                                                    where x.EmulatorID == emulatorId && x.PathType == pathType
                                                    select new { x.Path, x.EmulatorPathID }).ToDictionary(a => a.Path, b => b.EmulatorPathID);

            foreach (EmulatorPathConfig path in paths)
            {


                EmulatorPath ep = null;
                bool insertPath = true;
                if (!insert && currentPaths.ContainsKey(path.Path))
                {
                    //update
                    ep = db.EmulatorPath.Single(x => x.EmulatorPathID == currentPaths[path.Path]);
                    insertPath = false;
                }
                else
                {
                    // insert
                    ep = new EmulatorPath();
                    ep.EmulatorID = emulatorId;
                    ep.PathType = pathType;
                }

                // set values
                ep.IncludeSubPaths = (byte)(path.IncludeSubPaths == true ? 1 : 0);
                ep.SearchPattern = path.SearchPattern;
                ep.Path = path.Path;

                if (insertPath)
                    db.EmulatorPath.InsertOnSubmit(ep);

                try
                {
                    db.SubmitChanges();
                }
                catch (Exception e)
                {
                    error = "Saving path " + pathType + " [" + path.Path + "] : " + e.Message;
                    return false;
                }

            }
            // delete any paths no longer in there...
            foreach (KeyValuePair<string, int> item in currentPaths)
            {
                bool found = false;
                foreach (EmulatorPathConfig path in paths)
                {
                    if (item.Key == path.Path)
                    {
                        found = true;
                        break;
                    }
                }

                if (!found)
                {
                    // delete
                    EmulatorPath ep = db.EmulatorPath.Single(x => x.EmulatorPathID == item.Value);
                    db.EmulatorPath.DeleteOnSubmit(ep);

                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting path " + pathType + " [" + item.Key + "] : " + e.Message;
                        return false;
                    }
                }
            }
            return true;
        }

        public static bool deleteEmulatorConfig(int emulatorId, ref string error)
        {
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    // delete all menu collections for specific games
                    db.MenuCollection.DeleteAllOnSubmit(from m in db.Menu
                                                        join mc in db.MenuCollection on m.MenuID equals mc.MenuID
                                                        join g in db.Game on mc.GameID equals g.GameID
                                                        join e in db.Emulator on g.EmulatorID equals e.EmulatorID
                                                        where e.EmulatorID == emulatorId
                                                        select mc);


                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting menu records for games in emulator : " + e.Message;
                        return false;
                    }

                    // delete all game records
                    db.Game.DeleteAllOnSubmit(from g in db.Game
                                              where g.EmulatorID == emulatorId
                                              select g);
                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting game records for emulator :" + e.Message;
                        return false;
                    }

                    // delete all dependant path's
                    db.EmulatorPath.DeleteAllOnSubmit(from ep in db.EmulatorPath
                                                      where ep.EmulatorID == emulatorId
                                                      select ep);
                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting emulator paths : " + e.Message;
                        return false;
                    }

                    // finally delete the actual emulator record
                    db.Emulator.DeleteOnSubmit((from e in db.Emulator
                                               where e.EmulatorID == emulatorId
                                               select e).Single());
                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting emulator record : " + e.Message;
                        return false;
                    }

                    return true;
                }
            }
        }

        public static bool deleteCustomGame(int gameId, ref string error)
        {
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    // delete any menu collections this game is part of...
                    db.MenuCollection.DeleteAllOnSubmit(from m in db.Menu
                                                        join mc in db.MenuCollection on m.MenuID equals mc.MenuID
                                                        join g in db.Game on mc.GameID equals g.GameID
                                                        where g.GameID == gameId
                                                        select mc);
                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting menu records for custom game : " + e.Message;
                        return false;
                    }


                    // delete the actual game record
                    db.Game.DeleteOnSubmit((from g in db.Game
                                                where g.GameID == gameId
                                                select g).Single());
                    try
                    {
                        db.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        error = "Deleting custom game : " + e.Message;
                        return false;
                    }

                    return true;
                }
            }

        }

        /// <summary>
        /// simple code to execute all the statements in a sqlce file (split up by ';')
        /// </summary>
        /// <returns>error message (if any)</returns>
        public static string executeDBScript(bool executeDrops)
        {
            string script = "misc\\createDb.sqlce";

            // need to open up script, split by ';', then execute each part individually..
            string sql = File.ReadAllText(script);
            string[] sqlSteps = sql.Split(';');

            StringBuilder errorLog = new StringBuilder();

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                
                ceConn.Open();
                foreach (string sqlStep in sqlSteps)
                {
                    if (sqlStep.Trim() != "" && !sqlStep.StartsWith("--"))
                    {
                        if(!(!executeDrops && sqlStep.TrimStart().ToUpper().StartsWith("DROP "))) {

                            using (SqlCeCommand ceComm = new SqlCeCommand(sqlStep, ceConn))
                            {
                                try
                                {
                                    ceComm.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    errorLog.AppendLine("Error executing [" + sqlStep + "] : " + e.Message);
                                }

                            }
                        }
                    }
                }

            }
            return errorLog.ToString();
        }

        public static void populateMameCategories_wizard(int parentMenuId, KeyValuePair<int,string> emulator, string Name, SearchType NameSearchType,
            string RomFile, SearchType RomFileSearchType, string NPlayers, bool AllowClones, string[] DriverStatus,
            string[] controlType, string[] manufacturers, int maxNumButtons, string[] sourceFiles)
        {
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    // clear out all categories currently underneath selected menu item...
                    
                     db.MenuCollection.DeleteAllOnSubmit(    
                                (from m in db.Menu
                                 join mc in db.MenuCollection on m.MenuID equals mc.MenuID
                                 where m.ParentMenuID == parentMenuId
                                 select mc).ToList()
                                 );

                     db.Menu.DeleteAllOnSubmit(
                         (from m in db.Menu
                          where m.ParentMenuID == parentMenuId
                          select m).ToList()
                          );

                     db.SubmitChanges();


                    string sql = getSearchQuerySQL(emulator, Name, NameSearchType, RomFile, RomFileSearchType, NPlayers, "", AllowClones,
                     DriverStatus, controlType, manufacturers, maxNumButtons, sourceFiles, "category");
                    SqlCeDataAdapter da = new SqlCeDataAdapter(sql, ceConn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);

                    List<SearchGame> games = new List<SearchGame>();
                    foreach (DataRow dr in dt.Rows)
                    {
                        SearchGame game = new SearchGame()
                        {
                             Name = dr["Name"].ToString()
                            ,Emulator = dr["Emulator"].ToString()
                            ,Description = dr["Description"].ToString()
                            ,RomFile = dr["RomFile"].ToString()
                            ,DriverStatus = dr["DriverStatus"].ToString()
                            ,CloneOf = dr["CloneOf"].ToString()
                            ,NPlayers = dr["NPlayers"].ToString()
                            ,GameID = (int)dr["GameID"]
                            ,Category = dr["Category"].ToString()
                        };
                        games.Add(game);
                    }


                     //var query = getSearchQuery(db, emulator, Name, NameSearchType, RomFile, RomFileSearchType, NPlayers, "", AllowClones,
                     //DriverStatus, controlType, manufacturers, maxNumButtons, sourceFiles);

                    List<string> categories = (from g in games
                                               where g.Category != "[Unknown]"
                                               select g.Category).Distinct().ToList();
                    categories.Sort();

                     //List<string> categories  = (from q in query
                     //        join mrc in db.MameRomCategory on q.MameRomID equals mrc.MameRomID
                     //        join mc in db.MameCategory on mrc.MameCategoryID equals mc.MameCategoryID
                     //           where mc.CategoryName != "[Unknown]"
                     //        group mc by mc.CategoryName into grp
                     //           where grp.Count() > 0
                     //        select grp.Key).ToList();




                    // create all menus for each category under specified parentMenuID
                    int sort = 1;
                    foreach (string category in categories)
                    {
                        Menu m = new Menu()
                        {
                            Name = category
                            ,
                            Description = emulator.Value + " : " + category
                            ,
                            ParentMenuID = parentMenuId
                            ,
                            SortOrder = sort++
                            ,
                            MenuType = "standard"
                        };

                        db.Menu.InsertOnSubmit(m);

                    }
                    db.SubmitChanges();

                    Dictionary<string,int> categoryLookup = (from mc in db.MameCategory
                                                            join m in db.Menu on mc.CategoryName equals m.Name
                                                            where mc.CategoryName != "[Unknown]"
                                                            && m.ParentMenuID == parentMenuId
                                                            select new {
                                                                mc.CategoryName
                                                                , m.MenuID
                                                            }).ToDictionary(a=> a.CategoryName, b=> b.MenuID);

                    var newMenuCollection = (from g in games
                                             where g.Category != "[Unknown]"
                                             select new
                                             {
                                                 MenuID = categoryLookup[g.Category]
                                                 ,
                                                 GameID = g.GameID
                                             }).ToList();



                    //var newMenuCollection = (from q in query
                    //                         join mrc in db.MameRomCategory on q.MameRomID equals mrc.MameRomID
                    //                         join g in db.Game on q.MameRomID equals g.MameRomID
                    //                         join mc in db.MameCategory on mrc.MameCategoryID equals mc.MameCategoryID
                    //                         join mcc in db.Menu on mc.CategoryName equals mcc.Name
                    //                           where mc.CategoryName != "[Unknown]"
                    //                           && mcc.ParentMenuID == parentMenuId
                    //                         select new
                    //                         {
                    //                             MenuID = mcc.MenuID
                    //                             ,
                    //                             GameID = g.GameID
                    //                         }
                    //                           ).Distinct().ToList();





                    foreach (var item in newMenuCollection)
                    {
                        MenuCollection mc = new MenuCollection {
                             GameID = item.GameID
                             , MenuID = item.MenuID
                        };
                        db.MenuCollection.InsertOnSubmit(mc);
                    }
                    
                    db.SubmitChanges();
                }
            }
        }
        public static void populateGamesByLetter_wizard(int parentMenuId, KeyValuePair<int,string> emulator, string Name, SearchType NameSearchType,
            string RomFile, SearchType RomFileSearchType, string NPlayers, string Category, bool AllowClones, string[] DriverStatus,
            string[] controlType, string[] manufacturers, int maxNumButtons, string[] sourceFiles)
        {
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    // clear out all categories currently underneath

                    db.MenuCollection.DeleteAllOnSubmit(
                               (from m in db.Menu
                                join mc in db.MenuCollection on m.MenuID equals mc.MenuID
                                where m.ParentMenuID == parentMenuId
                                select mc).ToList()
                                );

                    db.Menu.DeleteAllOnSubmit(
                        (from m in db.Menu
                         where m.ParentMenuID == parentMenuId
                         select m).ToList()
                         );

                    db.SubmitChanges();


                    string sql = getSearchQuerySQL(emulator, Name, NameSearchType, RomFile, RomFileSearchType, NPlayers, "", AllowClones,
                     DriverStatus, controlType, manufacturers, maxNumButtons, sourceFiles, "");
                    SqlCeDataAdapter da = new SqlCeDataAdapter(sql, ceConn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);

                    List<SearchGame> games = new List<SearchGame>();
                    foreach (DataRow dr in dt.Rows)
                    {
                        SearchGame game = new SearchGame()
                        {
                            Name = dr["Name"].ToString()
                            ,
                            Emulator = dr["Emulator"].ToString()
                            ,
                            Description = dr["Description"].ToString()
                            ,
                            RomFile = dr["RomFile"].ToString()
                            ,
                            DriverStatus = dr["DriverStatus"].ToString()
                            ,
                            CloneOf = dr["CloneOf"].ToString()
                            ,
                            NPlayers = dr["NPlayers"].ToString()
                            ,
                            GameID = (int)dr["GameID"]
                        };
                        games.Add(game);
                    }


                    //var query = getSearchQuery(db, emulator, Name, NameSearchType, RomFile, RomFileSearchType, NPlayers, Category, AllowClones,
                    //    DriverStatus, controlType, manufacturers, maxNumButtons, sourceFiles);

                    List<string> categoryByChar = (from g in games
                                                   select g.Name.Substring(0, 1).ToUpper()).Distinct().ToList();

                    categoryByChar.Sort();

                    //List<string> categoryByChar = (
                    //    from g in query
                    //    group g by g.Name.Substring(0, 1).ToUpper() into firstChar
                    //    where firstChar.Count() > 0
                    //    select firstChar.Key.ToString()
                    //).ToList();





                    Dictionary<string, string> categoryLookup = new Dictionary<string, string>();
                    foreach (string categoryChar in categoryByChar)
                    {
                        string newCategory = "";
                        if (Char.IsLetter(categoryChar, 0))
                        {
                            newCategory = categoryChar;
                        }
                        else
                        {
                            newCategory = "Misc";
                        }
                        categoryLookup.Add(categoryChar, newCategory);
                    }

                    // work back other way now
                    int sort = 1;
                    foreach (string category in (from x in categoryLookup select x.Value).Distinct())
                    {
                        string[] chars = (from x in categoryLookup where x.Value == category select x.Key).ToArray();

                        // create MenuCollection.... (use category string)
                        Menu m = new Menu()
                        {
                            Name = category
                            ,
                            Description = emulator.Value + " : " + category
                            ,
                            ParentMenuID = parentMenuId
                            ,
                            SortOrder = sort++
                            ,
                            MenuType = "standard"
                        };
                        db.Menu.InsertOnSubmit(m);
                        db.SubmitChanges();
                        int menuId = m.MenuID;

                        //List<int> gameids =
                        //    (from g in db.Game
                        //     join mr in db.MameRom on g.MameRomID equals mr.MameRomID
                        //     where mr.DriverStatus == "good"
                        //     && chars.Contains(g.Name.Substring(0, 1).ToUpper())
                        //     select g.GameID).ToList();


                        List<int> gameids = (from g in games
                                             where chars.Contains(g.Name.Substring(0, 1).ToUpper())
                                             select g.GameID).Distinct().ToList();

                        //List<int> gameids =
                        //(from g in query
                        // where chars.Contains(g.Name.Substring(0, 1).ToUpper())
                        // select g.GameID).Distinct().ToList();

                        foreach (int gameId in gameids)
                        {
                            MenuCollection mc = new MenuCollection
                            {
                                GameID = gameId
                                ,
                                MenuID = menuId
                            };
                            db.MenuCollection.InsertOnSubmit(mc);

                        }
                        db.SubmitChanges();
                    }
                }
            }
        }

        public static List<ProgramConfig> getProgramConfig()
        {
            List<ProgramConfig> config = null;


            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    config = (from x in db.Configuration
                              select new ProgramConfig
                              {
                                  Name = x.Name
                                  ,
                                  DataType = x.DataType
                                  ,
                                  Description = x.Description
                                  ,
                                  Value = x.Value
                                  ,
                                  TypeSettings = x.TypeSettings
                                  ,
                                  Category = x.Category
                              }).ToList();
                }
            }
            return config;
        }

        public static void saveProgramConfig(List<ProgramConfig> configs)
        {
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    if (configs != null && configs.Count > 0)
                    {
                        foreach (ProgramConfig item in configs)
                        {

                            Configuration c = db.Configuration.Single(x => x.Name == item.Name);

                            if(c.Value != item.Value)
                            {
                                c.Value = item.Value;
                                db.SubmitChanges();
                            };
                        }
                    }


                }
            }

        }

        public static string getConfigItem(string configName)
        {
            string value = "";
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    value = (from x in db.Configuration
                                where x.Name == configName
                                select x.Value).SingleOrDefault().ToString();
                }
            }
            return value;
        }

        public static List<string> getThemeImageList()
        {
            string themePath = getConfigItem("Theme Path");

            List<string> imageList = new List<string>();
            string[] validExtensions = new string[] {".jpg",".bmp",".png"};

            DirectoryInfo di = new DirectoryInfo(themePath);
            if (di.Exists)
            {
                foreach (FileInfo file in di.GetFiles())
                {
                    if(validExtensions.Contains(file.Extension))
                        imageList.Add(file.Name);
                }
                imageList.Sort();
            }

            return imageList;

        }

        public static string GetMd5Sum(string str)
        {
            // First we need to convert the string into bytes, which
            // means using a text encoder.
            Encoder enc = System.Text.Encoding.Unicode.GetEncoder();

            // Create a buffer large enough to hold the string
            byte[] unicodeText = new byte[str.Length * 2];
            enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);

            // Now that we have a byte array we can ask the CSP to hash it
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);

            return Convert.ToBase64String(result);


        }

        public static Dictionary<string, string> getEmulatorPresets()
        {
            string filePath = Path.GetDirectoryName(Application.ExecutablePath) +  @"\DataFiles\emulator_list.xml";

            var emulatorFile = XElement.Load(filePath);

            Dictionary<string, string> emulators = (
                        from e in emulatorFile.Descendants("emulator")
                        select new
                        {
                            emulator = e.Attribute("machine").Value + " (" + e.Attribute("software").Value + ")"
                            ,
                            commandline = e.Value
                        }
                        ).ToDictionary(a => a.emulator, b=> b.commandline);

            return emulators;
        }

        public static Dictionary<string, string> getMameFileInfo()
        {
            Dictionary<string, string> fileInfo = new Dictionary<string, string>();

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    fileInfo = (from mfi in db.MameFileInfo
                                 select new
                                 {
                                     mfi.FileName
                                     ,
                                     mfi.Details
                                 }).ToDictionary(a => a.FileName, b => b.Details);
                }
            }
            return fileInfo;

        }


        public static string KeyMappingToString(Dictionary<string,string> KeyMappings)
        {
            if (KeyMappings == null || KeyMappings.Count == 0)
                return "";

            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            bool start = true;
            foreach (KeyValuePair<string, string> item in KeyMappings)
            {
                if (start)
                    start = false;
                else
                    sb.Append(",");

                sb.Append("{\"" + item.Key + "\"},{" + (item.Value == null ? "null" : "\"" + item.Value + "\"") + "}");

            }

            sb.Append("}");
            return sb.ToString();

        }

        public static Dictionary<string, string> StringToKeyMapping(string value)
        {

            if (value == null || value.Trim() == "")
                return null;

            if (!value.StartsWith("{{") || !value.EndsWith("}}"))
                return null;

            string temp = value.Substring(2, value.Length - 4); // ignore the two start and end brackets

            string[] aTemp = temp.Split(',');


            Dictionary<string, string> KeyMappings = new Dictionary<string, string>();
            for (int i = 0; i < aTemp.Length; i += 2)
            {
                string itemOne = aTemp[i].Trim(new char[] { '{', '\"', '}' });
                string itemTwo = aTemp[i + 1].Trim(new char[] { '{', '\"', '}' });

                if (itemTwo == "null")
                    itemTwo = null;
                KeyMappings.Add(itemOne, itemTwo);

            }

            return KeyMappings;
        }


        public static bool generateMissingResourceReport(string fileName)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("<html><body><h1>Games with missing resources</h1>" +
                    "<table border=\"1\"><tr><th>Name</th><th>Emulator</th><th>Rom File</th><th>Snapshot</th><th>Movie</th></tr>");
                using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
                {

                    ceConn.Open();

                    using (LauncherDB db = new LauncherDB(ceConn)) // linq
                    {

                        var missingResourceGames = (from g in db.Game
                                                    join e in db.Emulator on g.EmulatorID equals e.EmulatorID
                                                    where (g.SnapshotPathID == null
                                                    || g.MoviePathID == null)
                                                    && g.EmulatorID > 0
                                                    orderby e.Name ascending
                                                    select new
                                                    {
                                                        g.Name
                                                        ,
                                                        Emulator = e.Name
                                                        ,
                                                        g.RomFile
                                                        ,
                                                        g.SnapshotFile
                                                        ,
                                                        g.MovieFile
                                                    }).ToList();

                        foreach (var missingResourceGame in missingResourceGames)
                        {
                            sb.AppendLine("<tr><th>" + missingResourceGame.Name + "</th><td>" + missingResourceGame.Emulator + "</td><td>" + missingResourceGame.RomFile + "</td>" +
                                "<td>" + (missingResourceGame.SnapshotFile ?? "&nbsp;") + "</td><td>" + (missingResourceGame.MovieFile ?? "&nbsp;") + "</td></tr>");
                        }
                        sb.AppendLine("</table></body></html>");
                    }
                }

                File.WriteAllText(fileName, sb.ToString());
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }


        public static bool generateGamesReport(string fileName)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("<html><body><h1>Games Overview</h1>");

                //    "<table border=\"1\"><tr><th>Name</th><th>Emulator</th><th>Rom File</th><th>Snapshot</th><th>Movie</th></tr>");
                using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
                {

                    ceConn.Open();

                    using (LauncherDB db = new LauncherDB(ceConn)) // linq
                    {

                        var emulators = (from e in db.Emulator
                                         select new { e.Name, e.IsMame, e.Location, e.EmulatorID }).ToList();

                        foreach (var emulator in emulators)
                        {

                            sb.AppendLine("<h2>" + emulator.Name + "</h2>");
                            sb.AppendLine("<h3>Location: " + emulator.Location + "</h3>");
                            sb.AppendLine("<h3>Is Mame?: " + (emulator.IsMame==1?"Yes":"No") + "</h3>");

                            var emulatorPaths = (from ep in db.EmulatorPath
                                                 join g in db.Game on ep.EmulatorPathID equals g.RomPathID
                                                 where ep.EmulatorID == emulator.EmulatorID
                                                 && ep.PathType == "ROM"
                                                 group ep by ep.Path into g
                                                 select new { Path = g.Key, Count = g.Count() }).ToList();

                            foreach (var emulatorPath in emulatorPaths)
                            {
                                sb.AppendLine(emulatorPath.Path + " : " + emulatorPath.Count.ToString() + "<br />");
                            }
                            sb.AppendLine("<br /><br />");
                        }


                        sb.AppendLine("</body></html>");
                    }
                }

                File.WriteAllText(fileName, sb.ToString());
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static void displayHTMLDocument(string fileName)
        {
            ProcessStartInfo theProcess = new ProcessStartInfo(fileName);
            theProcess.WindowStyle = ProcessWindowStyle.Normal;
            theProcess.UseShellExecute = true;
            


            try
            {
                Process process = Process.Start(theProcess);
            }
            catch (Exception e)
            {
                // set back to standard, since process isn't running 
                string errorMessage = "Error display html file !" + Environment.NewLine
                    + "Message: " + e.Message;
                //return false;
            }
        }

        public static void remapDriveLetters(string fromDrive, string toDrive)
        {

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {

                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    List<EmulatorPath> paths = (from ep in db.EmulatorPath
                                 where ep.Path.StartsWith(fromDrive + ":")
                                 select ep).ToList();

                    foreach (EmulatorPath path in paths)
                    {
                        string currentPath = path.Path.ToString();

                         string newPath = currentPath.Replace(fromDrive + ":", toDrive + ":");
                         newPath = currentPath.Replace(fromDrive.ToUpper() + ":", toDrive.ToUpper() + ":");
                         path.Path = newPath;

                    }
                    db.SubmitChanges();
                }
            }

        }


    }

}
